package com.xinchuang.sqlite;

import lombok.extern.slf4j.Slf4j;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

/**
 * @ClassName DbConnManager
 * @Description: TODO(Sqlite链接)
 * @Author zhangxuan
 * @Date 2021-01-06 15:50
 * @Verion V1.0
 */
@Slf4j
public class DbConnManager {

    /**
     *  目标主信息表
     */
    private static final String CREATE_TARGET_MAIN_SQL = "CREATE TABLE IF NOT EXISTS target_table (id LONG, `key` TEXT, key_name TEXT, `key_type` TEXT, key_value TEXT, remark TEXT, person TEXT, `source` TEXT, insert_time LONG, update_time LONG, upload_time LONG, delete_time LONG, data_state LONG, PRIMARY KEY (id))";

    /**
     *  目标其他信息
     */
    private static final String CREATE_TARGET_ELSE_DATA_SQL = "CREATE TABLE IF NOT EXISTS target_else_table (id LONG, achievement_id LONG, `key` TEXT, key_name TEXT, `key_type` TEXT, key_value TEXT, remark TEXT, person TEXT, `source` TEXT, insert_time LONG, update_time LONG, upload_time LONG, delete_time LONG, data_state LONG, PRIMARY KEY (id))";

    /**
     *  目标专有信息
     */
    private static final String CREATE_TARGET_FUNCTION_DATA_SQL = "CREATE TABLE IF NOT EXISTS target_function_table (id LONG, achievement_id LONG, `key` TEXT, key_name TEXT, `key_type` TEXT, key_value TEXT, remark TEXT, person TEXT, `source` TEXT, insert_time LONG, update_time LONG, upload_time LONG, delete_time LONG, data_state LONG, PRIMARY KEY (id))";

    /**
     *  子目标信息表
     */
    private static final String CREATE_TARGET_SUB_DATA_SQL = "CREATE TABLE IF NOT EXISTS target_sub_table (id LONG, child_id LONG, `key` TEXT, key_name TEXT, `key_type` TEXT, key_value TEXT, remark TEXT, person TEXT, `source` TEXT, insert_time LONG, update_time LONG, upload_time LONG, delete_time LONG, data_state LONG, PRIMARY KEY (id))";

    /**
     *  子目标详细信息
     */
    private static final String CREATE_TARGET_SUB_ELSE_DATA_SQL = "CREATE TABLE IF NOT EXISTS target_sub_else_table (id LONG, child_id LONG, `key` TEXT, key_name TEXT, `key_type` TEXT, key_value TEXT, remark TEXT, person TEXT, `source` TEXT, insert_time LONG, update_time LONG, upload_time LONG, delete_time LONG, data_state LONG, PRIMARY KEY (id))";

    /**
     *  目标与子目标关联表
     */
    private static final String CREATE_TARGET_SUB_RELATION_SQL = "CREATE TABLE IF NOT EXISTS target_relation_table (id LONG, target_id LONG, child_name TEXT, child_num LONG, version LONG, remark TEXT, PRIMARY KEY (id))";

    /**
     *  版本控制
     */
    private static final String CREATE_VERSION_SQL = "CREATE TABLE IF NOT EXISTS version_table (id LONG, target_number LONG, version TEXT, publish_num TEXT, made_time LONG, achievement_type TEXT, keyword TEXT, unit TEXT, remark TEXT, insert_time LONG, delete_time LONG, data_state LONG, PRIMARY KEY (id))";

    public static Connection conn;

    /**
     *@author zhangxuan
     *@Description TODO 创建SQLITE连接
     *@Date 16:24 2021/1/6
     *@Param [sqliteUrl]
     *@return java.sql.Connection
     **/
    public static Connection getDbConn(String sqliteUrl) {
        try {
            Class.forName("org.sqlite.JDBC");
            conn = DriverManager.getConnection("jdbc:sqlite:" + sqliteUrl);
            PreparedStatement ps = conn.prepareStatement(CREATE_TARGET_MAIN_SQL);
            ps.execute();
            ps = conn.prepareStatement(CREATE_TARGET_ELSE_DATA_SQL);
            ps.execute();
            ps = conn.prepareStatement(CREATE_TARGET_FUNCTION_DATA_SQL);
            ps.execute();
            ps = conn.prepareStatement(CREATE_TARGET_SUB_DATA_SQL);
            ps.execute();
            ps = conn.prepareStatement(CREATE_TARGET_SUB_ELSE_DATA_SQL);
            ps.execute();
            ps = conn.prepareStatement(CREATE_TARGET_SUB_RELATION_SQL);
            ps.execute();
            ps = conn.prepareStatement(CREATE_VERSION_SQL);
            ps.execute();
            ps.close();
        } catch (Exception e){
            log.error(e.getMessage(), e);
            return null;
        }
        return conn;
    }

    public Connection getConn(String sqliteUrl) {
        Connection conn = null;
        try {
            Class.forName("org.sqlite.JDBC");
            conn = DriverManager.getConnection("jdbc:sqlite:" + sqliteUrl);
            PreparedStatement ps = conn.prepareStatement(CREATE_TARGET_MAIN_SQL);
            ps.execute();
            ps = conn.prepareStatement(CREATE_TARGET_ELSE_DATA_SQL);
            ps.execute();
            ps = conn.prepareStatement(CREATE_TARGET_FUNCTION_DATA_SQL);
            ps.execute();
            ps = conn.prepareStatement(CREATE_TARGET_SUB_DATA_SQL);
            ps.execute();
            ps = conn.prepareStatement(CREATE_TARGET_SUB_ELSE_DATA_SQL);
            ps.execute();
            ps = conn.prepareStatement(CREATE_TARGET_SUB_RELATION_SQL);
            ps.execute();
            ps = conn.prepareStatement(CREATE_VERSION_SQL);
            ps.execute();
            ps.close();
        } catch (Exception e){
            log.error(e.getMessage(), e);
            return null;
        }
        return conn;
    }

}